﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using Top.Web;
using Top.Web.Api;
using Top.Core;
using Top.Data;
using Top.PointStore.Business;
using Top.PointStore.Business.Models;

namespace Top.PointStore.Business
{
    /*功能说明:商品兑换操作管理类
    *版本:
    *参与人:王士锋
    *修改日期:2015.3.4
    */
    /// <summary>商品兑换
    /// </summary>
    public static class ProductsExchangeManager
    {

        /// <summary>检查是否存在
        /// </summary>
        public static async Task<bool> Exists(int userKid, int productKid)
        {
            if (userKid <= 0) return false;
            if (productKid <= 0) return false;
            using (var db = new DbTour())
            {
                var query = db.Table("select count(*) from T_ProductsExchange_new");
                query.And("F_UserKid = {0}", userKid);
                query.And("F_ProductKid = {0}", productKid);
                //query.And("CONVERT(varchar(100),F_ExchangeTime,23) = {0}", DateTime.Now.ToString("yyyy-MM-dd"));
                return await query.ExecuteScalar<int>() > 0;
            }
        }

        /// <summary>
        /// <para>-1:经纪人id有误</para>
        /// <para>-2:商品id有误</para>
        /// </summary>
        /// <param name="userKid"></param>
        /// <param name="productKid"></param>
        /// <returns></returns>
        public static async Task<int> IsCheckNum(int userKid, int productKid)
        {
            if (userKid <= 0) return -1;
            if (productKid <= 0) return -2;
            using (var db = new DbTour())
            {
                var query = db.Table("select count(*) from T_ProductsExchange_new");
                query.And("F_UserKid = {0}", userKid);
                query.And("F_ProductKid = {0}", productKid);
                query.And("CONVERT(varchar(100),F_ExchangeTime,23) = {0}", DateTime.Now.ToString("yyyy-MM-dd"));

                return await query.ExecuteScalar<int>();
            }
        }

        public static async Task<int> ShipInfo(int exchangeId, string consignee, string mobile, string companyname,
            string companyaddress)
        {
            if (exchangeId <= 0)
                return -1;
            if (string.IsNullOrWhiteSpace(consignee))
                return -2;
            if (string.IsNullOrWhiteSpace(mobile))
                return -3;
            if (string.IsNullOrWhiteSpace(companyname))
                return -4;
            if (string.IsNullOrWhiteSpace(companyaddress))
                return -5;

            const string sql = @"
                    UPDATE T_ProductsExchange_new
	                    SET  F_Consignee = {1}
		                    ,F_Mobile = {2}
		                    ,F_CompanyName = {3}
		                    ,F_CompanyAddress = {4}
	                    WHERE Kid={0}";
            using (var db = new DbTour())
            {
                return await db.Sql(sql, exchangeId, consignee, mobile, companyname, companyaddress).ExecuteNonQuery();
            }
        }


        /// <summary>增加一条数据
        /// <para>大于0为更新成功</para>
        /// <para>0:更新失败</para>
        /// <para>-1:商品id有误</para>
        /// <para>-2:经纪人id有误</para>
        /// <para>-3:兑换记录状态有误</para>
        /// <para>-4:兑换记录id有误</para>
        /// </summary>
        public static async Task<int> Add(T_ProductsExchange_new model, DbTour db)
        {
            var checkRes = CheckingT_ProductsExchange(model);
            if (checkRes < 0) return checkRes;
            const string sql = @"insert into T_ProductsExchange_new (F_UserToken,F_ProductKid,F_ExchangeTime,F_PointNum,F_OrderNo,F_Guid,F_IsStatus,F_Remark,F_ExchangeType,F_IsDisplay) 
                values ({0:F_UserToken},
                        {0:F_ProductKid},
                        {0:F_ExchangeTime},
                        {0:F_PointNum},
                        {0:F_OrderNo},
                        {0:F_Guid},
                        {0:F_IsStatus},
                        '',
                        {0:F_ExchangeType},
                        {0:F_IsDisplay});
                select LAST_INSERT_ID()";
            return await db.Sql(sql, model).ExecuteScalar<int>();
        }

        public static async Task<int> Add(T_ProductsExchange_new model)
        {
            var checkRes = CheckingT_ProductsExchange(model);
            if (checkRes < 0) return checkRes;
            const string sql = @"insert into T_ProductsExchange_new (F_UserToken,F_ProductKid,F_ExchangeTime,F_PointNum,F_OrderNo,F_Guid,F_IsStatus,F_Remark,F_ExchangeType,F_IsDisplay) 
                values ({0:F_UserToken},
                        {0:F_ProductKid},
                        {0:F_ExchangeTime},
                        {0:F_PointNum},
                        {0:F_OrderNo},
                        {0:F_Guid},
                        {0:F_IsStatus},
                        '',
                        {0:F_ExchangeType},
                        {0:F_IsDisplay});
                select LAST_INSERT_ID()";
            using (var db = new DbTour())
            {
                return await db.Sql(sql, model).ExecuteScalar<int>();
            }            
        }

        /// <summary>根据编号得到一条数据
        /// </summary>
        /// <param name="kid"></param>
        /// <returns></returns>
        public static async Task<T_ProductsExchangeExpand> Get(int kid)
        {
            if (kid <= 0) return new T_ProductsExchangeExpand();
            const string sql = @"select 
--e.Kid,e.F_ProductKid,e.F_ExchangeTime,e.F_PointNum,e.F_IsStatus,e.F_OrderNo,e.F_Guid,e.F_Remark,e.F_ExchangeDescription,e.F_ExchangeType,
e.*,
p.F_ProductName,p.F_Description,p.F_PicUrl,i.F_Source
from T_ProductsExchange_new as e
left  join T_PointProduct as p on e.F_ProductKid=p.Kid
left join T_ProductInventory as i on p.Kid=i.F_ProductKid
where e.Kid={0} limit 1";
            using (var db = new DbTour())
            {
                return await db.Sql(sql, kid).FirstOrDefault<T_ProductsExchangeExpand>();
            }
        }
        /// <summary>T_ProductsExchange_new模型参数验证
        /// <para>0:验证成功</para>
        /// <para>-1:商品id有误</para>
        /// <para>-2:经纪人id有误</para>
        /// <para>-3:兑换记录状态有误</para>
        /// <para>-4:兑换记录id有误</para>
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        private static int CheckingT_ProductsExchange(T_ProductsExchange_new model)
        {
            if (model.F_ProductKid <= 0) return -1;
            if (string.IsNullOrEmpty(model.F_UserToken)) return -2;
            if (!System.Enum.IsDefined(typeof(ExchangeState), model.F_IsStatus))
                return -3;
            if (model.Kid < 0)
                return -4;
            return 0;
        }

        /// <summary>更新一条数据
        /// <para>大于0为更新成功</para>
        /// <para>0:更新失败</para>
        /// <para>-1:商品id有误</para>
        /// <para>-2:经纪人id有误</para>
        /// <para>-3:兑换记录状态有误</para>
        /// <para>-4:兑换记录id有误</para>
        /// </summary>
        public static async Task<int> Update(T_ProductsExchange_new model)
        {
            var checkRes = CheckingT_ProductsExchange(model);
            if (checkRes < 0) return checkRes;
            const string sql = @"update T_ProductsExchange_new set 
                F_UserKid={0:F_UserKid},
                F_ProductKid={0:F_ProductKid},
                F_ExchangeTime={0:F_ExchangeTime},
                F_PointNum={0:F_PointNum},
                F_IsStatus={0:F_IsStatus},
                F_Remark={0:F_Remark}
              where Kid={0:Kid}";

            using (var db = new DbTour())
            {
                return await db.Sql(sql, model).ExecuteNonQuery();
            }
        }

        public static async Task<int> Update(string kid, string field)
        {
            string sql = string.Format(@"update T_ProductsExchange_new set {0} where Kid={1}", field, kid);
            using (var db = new DbTour())
            {
                return await db.Sql(sql).ExecuteNonQuery();
            }
        }


        /// <summary>根据用户编号获取数据列表
        /// </summary>
        /// <param name="userKid"></param>
        /// <returns></returns>
        public static async Task<IList<T_ProductsExchangeExpand>> GetList(int cityKid, int authCityKid, Pager pager, int isStatus, int categoryKid, string sdt, string edt,
            string orderNO, string productName, string brokerName, string brokerPhone, int lowPoint, int topPoint, int exchangeType, string agentStores, string expandingHeader)
        {
            #region 清除字符串两边空格

            sdt = sdt.Trim();
            edt = edt.Trim();
            orderNO = orderNO.Trim();
            productName = productName.Trim();
            brokerName = brokerName.Trim();
            brokerPhone = brokerPhone.Trim();

            #endregion

            using (var db = new DbTour("default"))
            {
                var query = db.Table(@"
                                    select  
	                                    distinct 
                                        a.F_UserToken,
	                                    a.Kid,
	                                    c.F_ParentKid,
	                                    F_ExchangeTime,
	                                    a.F_OrderNo,
	                                    F_IsStatus,
	                                    a.F_Remark,
	                                    b.F_ProductName,
                                        b.F_PicUrl,
                                        c.F_CategoryName,
                                        a.F_Consignee,
                                        a.F_PointNum,
                                        a.F_Mobile,
                                        a.F_CompanyName,
                                        a.F_CompanyAddress,
                                        f.F_Name,
                                        h.F_Title,
                                        H.F_Phone
                                    from T_ProductsExchange_new a 
                                    left join T_PointProduct b on a.F_ProductKid=b.Kid 
                                    left join T_ProductsCategory c on b.F_CategoryKid=c.Kid                                
                                    left join T_ExchangeRule d on a.F_ProductKid=d.F_ProductKid
                                    left join T_RuleToCity e on d.Kid=e.Kid                                    
                                    left join T_OAuthInfo g on a.F_UserToken=g.F_UserToken
                                    left join T_Broker h on g.F_BrokerKid=h.kid
                                    left join T_ProvinceCityArea f on h.F_CityKid=f.Kid
                                    left join T_BrokerCompany i on h.F_BrokerCompanyKid=i.Kid
                                    left join S_Admin j on i.F_DirectorAdminKid=j.Kid
                                    ");
                query.And("a.F_IsDisplay<>0");

                #region 搜索  orderNO,productName,brokerName,brokerPhone,lowPoint,topPoint


                if (authCityKid != 0)
                {
                    query.And(" h.F_CityKid = {0}", authCityKid);
                }
                else
                {
                    if (cityKid > 0)
                    {
                        query.And(" h.F_CityKid = {0}", cityKid);
                    }
                }

                //List<int> frameworkKisList = await SystemManager.GetFrameworkKids(adminKid);
                //if (frameworkKisList.Contains(1))
                //{

                //}
                //else
                //{

                //    IList<AdminCityExpand> cityList = await SystemManager.GetCityListByAdminKid(adminKid);
                //    List<int> kids = (from c in cityList select c.F_CityKid).ToList<int>();
                //    if (kids.Any())
                //    {
                //        //string kidList = string.Join(",", kids);
                //        query.And(" h.F_CityKid in ({0})", kids);
                //    }
                //}


                //if (cityKid > 0)
                //{
                //    query.And(" h.F_CityKid = {0}", cityKid);
                //}

                if (isStatus >= 0)
                {
                    query.And("a.F_IsStatus={0}", isStatus);
                }
                if (categoryKid > 0)
                {
                    query.And("b.F_CategoryKid={0}", categoryKid);
                }
                if (!string.IsNullOrWhiteSpace(orderNO))
                {
                    query.And("a.F_OrderNo like {0}", "%" + orderNO + "%");
                }
                if (!string.IsNullOrWhiteSpace(productName))
                {
                    query.And("b.F_ProductName like {0}", "%" + productName + "%");
                }
                if (!string.IsNullOrWhiteSpace(brokerName))
                {
                    query.And("h.F_Title like {0}", "%" + brokerName + "%");
                }
                if (!string.IsNullOrWhiteSpace(brokerPhone))
                {
                    query.And("h.F_Phone like {0}", "%" + brokerPhone + "%");
                }
                if (lowPoint > 0)
                {
                    query.And("a.F_PointNum >= {0}", lowPoint);
                }

                if (topPoint > 0)
                {
                    query.And("a.F_PointNum <= {0}", topPoint);
                }
                if (exchangeType > 0)
                {
                    switch (exchangeType)
                    {
                        case 1:
                            query.And("d.F_IsMeskill = {0}", 1);
                            break;
                        case 2:
                            query.And("a.F_ExchangeType='幸运大转盘'");
                            query.And("d.F_IsMeskill = {0}", 0);
                            break;
                        case 3:
                            query.And("a.F_ExchangeType='积分兑换'");
                            query.And("d.F_IsMeskill = {0}", 0);
                            break;
                        default:
                            break;
                    }
                }
                if (!string.IsNullOrWhiteSpace(agentStores))
                {
                    query.And("i.F_Title like {0}", "%" + agentStores + "%");
                }
                if (!string.IsNullOrWhiteSpace(expandingHeader))
                {
                    query.And("j.F_RealName like {0}", "%" + expandingHeader + "%");
                }

                DateTime startTime;
                if (DateTime.TryParse(sdt, out startTime))
                {
                    query.And("a.F_ExchangeTime >='" + startTime + "'");
                }
                DateTime endTime;
                if (DateTime.TryParse(edt, out endTime))
                {
                    endTime = endTime.AddDays(1);
                    query.And("a.F_ExchangeTime <'" + endTime + "'");
                }

                #endregion
                //query.And("a.F_ExchangeType='积分兑换'");
                return await query.ToList<T_ProductsExchangeExpand>(pager.StartNumber, pager.EndNumber, "a.F_ExchangeTime desc");
            }
        }

        /// <summary>
        /// 根据用户ID获取所兑换的商品
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="user"></param>
        /// <returns></returns>
        public static async Task<IList<T_ProductsExchangeExpand>> GetList(Pager pager, OAuthUser user)
        {
            if (user == null) return new T_ProductsExchangeExpand[0];
            using (var db = new DbTour())
            {
                var query = db.Table(@"select a.*,ifnull(b.F_ProductName,'') as F_ProductName,
                    ifnull(b.F_PicUrl,'') as  F_PicUrl
                    from T_ProductsExchange_new a 
                    left join T_PointProduct b on a.F_ProductKid=b.Kid");
                query.And("a.F_UserToken={0}", user.UserToken.ToString());
                query.And("a.F_IsStatus <>8 ");
                query.And("a.F_IsDisplay<>0");
                return await query.ToList<T_ProductsExchangeExpand>(pager.StartNumber, pager.EndNumber, "a.F_ExchangeTime desc");
            }
        }

        /// <summary>根据兑换时间和商品id获取数据列表
        /// </summary>
        /// <param name="productKid">商品编号</param>
        /// <param name="beginTime">查询开始时间</param>
        /// <param name="endTime">查询结束时间</param>
        /// <returns></returns>
        public static async Task<List<T_ProductsExchange_new>> GetListByProductKid(int productKid, DateTime beginTime, DateTime endTime)
        {
            if (productKid <= 0) return new List<T_ProductsExchange_new>();
            if (beginTime > endTime)
            {
                var temp = beginTime;
                beginTime = endTime;
                endTime = temp;
            }
            const string sql = "select * from T_ProductsExchange_new where F_ProductKid = {0} and F_ExchangeTime >{1} and F_ExchangeTime <{2}";
            using (var db = new DbTour())
            {
                return await db.Sql(sql, productKid, beginTime, endTime).ToList<T_ProductsExchange_new>();
            }
        }
        /// <summary>根据兑换时间和经纪人id获取数据列表
        /// </summary>
        /// <param name="brokerKid">经纪人id</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public static async Task<List<T_ProductsExchange_new>> GetListByBrokerKid(int brokerKid, DateTime beginTime, DateTime endTime)
        {
            if (brokerKid <= 0) return new List<T_ProductsExchange_new>();
            if (beginTime > endTime)
            {
                var temp = beginTime;
                beginTime = endTime;
                endTime = temp;
            }
            const string sql = "select * from T_ProductsExchange_new where F_ProductKid = {0} and F_ExchangeTime >{1} and F_ExchangeTime <{2}";
            using (var db = new DbTour())
            {
                return await db.Sql(sql, brokerKid, beginTime, endTime).ToList<T_ProductsExchange_new>();
            }
        }

        /// <summary>追加备注
        /// </summary>
        /// <param name="kid">兑换记录id</param>
        /// <param name="remark">备注</param>
        /// <returns></returns>
        public static async Task<bool> AppendRemark(int kid, string remark)
        {
            if (kid <= 0) return false;
            if (string.IsNullOrEmpty(remark)) return true;

            remark = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss : ") + string.Join(" ", remark.Split(new[] { '\n', '\r', ' ' }, StringSplitOptions.RemoveEmptyEntries)) + Environment.NewLine;

            const string sql = @"update T_ProductsExchange_new set F_Remark = F_Remark + {0} where Kid={1}";
            using (var db = new DbTour())
            {
                return await db.Sql(sql, remark, kid).ExecuteNonQuery() > 0;
            }
        }
        /// <summary>保存约车券号码
        /// </summary>
        /// <param name="kid">兑换记录id</param>
        /// <param name="carNo">约车券号码</param>
        /// <returns></returns>
        public static async Task<bool> SaveCarNo(int kid, string carNo)
        {
            if (kid <= 0) return false;
            if (string.IsNullOrEmpty(carNo)) return true;

            //carNo = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss : ") + string.Join(" ", remark.Split(new[] { '\n', '\r', ' ' }, StringSplitOptions.RemoveEmptyEntries)) + Environment.NewLine;

            const string sql = @"update T_ProductsExchange_new set F_ExchangeDescription ={0} where Kid={1}";
            using (var db = new DbTour())
            {
                return await db.Sql(sql, carNo, kid).ExecuteNonQuery() > 0;
            }
        }
        /// <summary> 更新兑换记录状态和备注
        /// </summary>
        /// <param name="kid">兑换记录id</param>
        /// <param name="state">状态</param>
        /// <param name="remark">备注</param>
        /// <returns></returns>
        public static async Task<bool> UpdateStateWithRemark(int kid, ExchangeState state, string remark)
        {
            if (kid <= 0) return false;
            if (!System.Enum.IsDefined(typeof(ExchangeState), state)) return false;
            using (var db = new DbTour())
            {
                await db.Begin();
                if (!await UpdateState(kid, state)) return false;
                if (!await AppendRemark(kid, remark))
                {
                    db.Rollback();
                    return false;
                }
                db.Commit();
                return true;
            }
        }

        public static async Task<bool> Revoked(int kid, string remark)
        {
            if (kid <= 0) return false;
            const string sql = @"update T_ProductsExchange_new set F_IsStatus={0} where Kid={1} and {2} <> F_IsStatus and {0} <> F_IsStatus";
            using (var db = new DbTour())
            {
                return await db.Sql(sql, (int)ExchangeState.Revoked, kid, (int)ExchangeState.Expired).ExecuteNonQuery() > 0;
            }
        }

        /// <summary>更改状态
        /// </summary>
        /// <param name="kid">兑换记录id</param>
        /// <param name="state">状态</param>
        /// <returns></returns>
        public static async Task<bool> UpdateState(int kid, ExchangeState state)
        {
            if (kid <= 0) return false;
            if (state == ExchangeState.Revoked) return false;
            if (!System.Enum.IsDefined(typeof(ExchangeState), state)) return false;
            const string sql = @"update T_ProductsExchange_new set F_IsStatus={0} where Kid={1} and {0} >= F_IsStatus";
            using (var db = new DbTour())
            {
                var result = await db.Sql(sql, (int)state, kid).ExecuteNonQuery() > 0;
                if (result && state == ExchangeState.Expired)
                {
                    var exchange = await Get(kid);
                    await PointManager.UpdateUserPoint(exchange.F_UserToken, (int)(exchange.F_PointNum * 0.8), "broker.carexpired.return", db);//约车券过期返还积分
                }
                return result;
            }
        }

        public static async Task<bool> Revoke(int kid, int state, string remark)
        {
            if (kid <= 0) return false;
            var exchange = await Get(kid);
            if (exchange == null) return false;
            //判断是否为约车券
            if (!string.IsNullOrEmpty(exchange.F_Source))
            {
                //调用约车接口通知改状态
                string postData = string.Format("brokerKid={0}&exchangeId={1}&status={2}", exchange.F_UserToken, kid, "3");
                string result = WebHelper.SendMsg(Configs.AppSettings["carRevoke"], postData);

                //將返回的結果反序列化
                var res = Json.ToObject<Result>(result);

                //判断取消是否成功
                if (res.code == 0 && res.data == "true") return false;
            }
            using (var db = new DbTour())
            {
                try
                {
                    await db.Begin();
                    //改兑换记录状态
                    if (await Revoked(kid, remark))
                    {
                        //还积分
                       await PointManager.UpdateUserPoint(exchange.F_UserToken, exchange.F_PointNum, "broker.pintproduct.revoke", db);//兑换撤销返还积分
                    }
                    else
                    {
                        db.Rollback();
                        return false;
                    }
                    db.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    Trace.Write(e, "数据库操作异常!");
                    db.Rollback();
                    return false;
                }


            }



        }


        public static async Task<int> Send(int kid, string customerName, DateTime orderTime, string productName, string phone)
        {
            if (kid <= 0) return -1;
            const string sql = @"update T_ProductsExchange_new set F_IsStatus={0} where Kid={1}";
            int result;
            using (var db = new DbTour())
            {
                result = await db.Sql(sql, (int)ExchangeState.Send, kid).ExecuteNonQuery();
                SendNote(customerName, orderTime, productName, phone);
            }
            return result;
        }

        public static async Task<int> SendKids(string kids)
        {
            if (string.IsNullOrWhiteSpace(kids)) return -1;
            string sql = @"update T_ProductsExchange_new set F_IsStatus=10 where Kid in (" + kids + ")";
            int result;
            using (var db = new DbTour())
            {
                result = await db.Sql(sql).ExecuteNonQuery();
                SendNote(kids);
            }
            return result;
        }
        /// <summary>
        /// 订单审核
        /// </summary>
        /// <param name="kid"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public static async Task<int> Audit(int kid, int status)
        {
            if (kid <= 0) return -1;
            if (status <= 0) return -2;
            if (status > 10) return -2;

            const string sql = @"update T_ProductsExchange_new set F_IsStatus={0} where Kid={1}";

            if (status == 4)
            {
                await PointRefund(kid.ToString());                
            }
            int result = 0;
            using (var db = new DbTour())
            {
                result = await db.Sql(sql, status, kid).ExecuteNonQuery();
                if (status == 9)
                {
                    try
                    {
                        List<T_PointStoreInvoice> list = await PointStoreInvoiceManager.FromDBGetDespatchData(kid.ToString());
                        foreach (T_PointStoreInvoice item in list)
                        {
                            await PointStoreInvoiceManager.InsertToDB(item, db);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw;
                    }
                }
            }
            return result;
        }


        /// <summary>
        /// 批量审核
        /// </summary>
        /// <param name="kids"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public static async Task<int> AuditKids(string kids, int status)
        {
            if (string.IsNullOrWhiteSpace(kids)) return -1;
            if (status <= 0) return -2;
            if (status > 10) return -2;
            if (status == 4)
            {
                await PointRefund(kids);
            }
            string sql = string.Format("update T_ProductsExchange_new set F_IsStatus={0} where Kid in ({1})", status, kids);
            int result = 0;
            using (var db = new DbTour())
            {
                result = await db.Sql(sql).ExecuteNonQuery();
                if (status == 9)
                {
                    List<T_PointStoreInvoice> list = await PointStoreInvoiceManager.FromDBGetDespatchData(kids);
                    foreach (T_PointStoreInvoice item in list)
                    {
                        await PointStoreInvoiceManager.InsertToDB(item, db);
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 批量编辑
        /// </summary>
        /// <param name="kids"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public static async Task<int> EditKids(string kids, int status)
        {
            if (string.IsNullOrWhiteSpace(kids)) return -1;
            if (status <= 0) return -2;
            if (status > 10) return -2;
            string sql1 = @"select kid,F_UserToken,F_PointNum,F_ProductKid,F_IsStatus from T_ProductsExchange_new where Kid in ({0})";
            List<T_ProductsExchange_new> list = null;
            using (var db = new DbTour())
            {
                list = await db.Sql(sql1, kids).ToList<T_ProductsExchange_new>();
            }
            foreach (T_ProductsExchange_new item in list)
            {
                if (item.F_IsStatus == status)
                {
                    return 1;
                }
            }
            if (status == 9)
            {
                using (var db = new DbTour())
                {
                    foreach (T_ProductsExchange_new item in list)
                    {
                        await PointManager.UpdateUserPoint(item.F_UserToken, item.F_PointNum, "broker.point.cash", item.Kid, "SUBTRACT", db);
                        //更新库存
                        int affectedRows = await PointProductManager.UpdateField(item.F_ProductKid, db);
                        if (affectedRows <= 0)   //判断库存是否退还了该商品 
                        {
                            db.Rollback();
                            return -1;
                        }
                    }
                }
            }
            if (status == 4)
            {
                await PointRefund(kids);
            }
            string sql = string.Format("update T_ProductsExchange_new set F_IsStatus={0} where Kid in ({1})", status, kids);
            using (var db = new DbTour())
            {
                return await db.Sql(sql).ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 积分冲销
        /// </summary>
        /// <param name="kids">需要退还积分的商品兑换记录Kid</param>
        /// <returns></returns>
        public static async Task<int> PointRefund(string kids)
        {
            string sql = @"select kid,F_UserToken,F_PointNum,F_ProductKid from T_ProductsExchange_new where Kid in ({0})";
            sql = string.Format(sql, kids);
            List<T_ProductsExchange_new> list = null;
            using (var db = new DbTour())
            {
                list = await db.Sql(sql).ToList<T_ProductsExchange_new>();
                foreach (T_ProductsExchange_new item in list)
                {
                    await PointManager.UpdateUserPoint(item.F_UserToken, item.F_PointNum, "api.point.refund", item.Kid, "REFUND", db);
                    //更新库存
                    int affectedRows = await PointProductManager.RefundProduct(item.F_ProductKid, db);
                    if (affectedRows <= 0)   //判断库存是否退还了该商品 
                    {
                        db.Rollback();
                        return -1;
                    }
                    if (MemoryTable.TB_ProductShow_Plus.ContainsKey(item.F_ProductKid))
                    {
                        MemoryTable.TB_ProductShow_Plus[item.F_ProductKid].ExchangeNum -= 1;
                        MemoryTable.TB_ProductShow_Plus[item.F_ProductKid].F_InventoryNum += 1;
                    }
                }
            }
            return 1;
        }



        public static async Task<string> Export(int cityKid, int authCityKid, Pager pager, int isStatus, int categoryKid, string sdt, string edt,
            string orderNO, string productName, string brokerName, string brokerPhone, int lowPoint, int topPoint, int exchangeType, string agentStores, string expandingHeader)
        {
            #region 清除字符串两边空格

            sdt = sdt.Trim();
            edt = edt.Trim();
            orderNO = orderNO.Trim();
            productName = productName.Trim();
            brokerName = brokerName.Trim();
            brokerPhone = brokerPhone.Trim();

            #endregion

            using (var db = new DbTour())
            {
                var query = db.Table(@"
                                select 
                                    distinct
                                    f.F_Name as CityName,
                                    a.F_ExchangeTime,
                                    a.F_IsStatus,
                                    b.F_ProductName,
                                    1 as F_ExchangeNum,
                                    k.F_ProductPrice,
                                    a.F_PointNum,
                                    h.F_Title,                                    
                                    h.F_Phone,
                                    h.F_BrokerCompanyName,
                                    i.F_Address,
                                    a.F_Consignee,
                                    a.F_Mobile,
                                    a.F_CompanyName,
                                    a.F_CompanyAddress
                                from T_ProductsExchange_new a  
                                left join T_PointProduct b on a.F_ProductKid=b.Kid  
                                left join T_ProductsCategory c on b.F_CategoryKid=c.Kid                                
                                left join T_ExchangeRule d on a.F_ProductKid=d.F_ProductKid
                                left join T_RuleToCity e on d.Kid=e.Kid
                                left join T_OAuthInfo g on a.F_UserToken=g.F_UserToken
                                left join T_Broker h on g.F_BrokerKid=h.Kid
                                left join T_ProvinceCityArea f on h.F_CityKid=f.Kid
                                left join T_BrokerCompany i on h.F_BrokerCompanyKid=i.Kid
                                left join S_Admin j on i.F_DirectorAdminKid=j.Kid
                                left join T_ProductInventory k on a.F_ProductKid = k.F_ProductKid
                            ");
                query.And("a.F_IsDisplay<>0");
                #region 搜索  orderNO,productName,brokerName,brokerPhone,lowPoint,topPoint

                if (authCityKid != 0)
                {
                    query.And(" h.F_CityKid = {0}", authCityKid);
                }
                else
                {
                    if (cityKid > 0)
                    {
                        query.And(" h.F_CityKid = {0}", cityKid);
                    }
                }

                //List<int> frameworkKisList = await SystemManager.GetFrameworkKids(adminKid);
                //if (frameworkKisList.Contains(1))
                //{

                //}
                //else
                //{

                //    IList<AdminCityExpand> cityList = await SystemManager.GetCityListByAdminKid(adminKid);
                //    List<int> kids = (from c in cityList select c.F_CityKid).ToList<int>();
                //    if (kids.Any())
                //    {
                //        //string kidList = string.Join(",", kids);
                //        query.And(" h.F_CityKid in ({0})", kids);
                //    }
                //}


                #region old
                //if (cityKid > 0)
                //{
                //    query.And(" e.F_CityKid = {0}", cityKid);
                //}

                //if (isStatus >= 0)
                //{
                //    query.And("a.F_IsStatus={0}", isStatus);
                //}
                //if (categoryKid > 0)
                //{
                //    query.And("b.F_CategoryKid={0}", categoryKid);
                //}
                //if (!string.IsNullOrWhiteSpace(orderNO))
                //{
                //    query.And("a.F_OrderNo like {0}", "%" + orderNO + "%");
                //}
                //if (!string.IsNullOrWhiteSpace(productName))
                //{
                //    query.And("b.F_ProductName like {0}", "%" + productName + "%");
                //}
                ////if (!string.IsNullOrWhiteSpace(brokerName))
                ////{
                ////    query.And("x.F_Title like {0}", "%" + brokerName + "%");
                ////}
                ////if (!string.IsNullOrWhiteSpace(brokerPhone))
                ////{
                ////   query.And("x.F_Phone like {0}", "%" + brokerPhone + "%");
                ////
                //// }
                //if (lowPoint > 0)
                //{
                //    query.And("a.F_PointNum >= {0}", lowPoint);
                //}

                //if (topPoint > 0)
                //{
                //    query.And("a.F_PointNum <= {0}", topPoint);
                //}

                //if (exchangeType > 0)
                //{
                //    switch (exchangeType)
                //    {
                //        case 1:
                //            query.And("d.F_IsMeskill = {0}", 1);
                //            break;
                //        case 2:
                //            query.And("a.F_ExchangeType='幸运大转盘'");
                //            query.And("d.F_IsMeskill = {0}", 0);
                //            break;
                //        case 3:
                //            query.And("a.F_ExchangeType='积分兑换'");
                //            query.And("d.F_IsMeskill = {0}", 0);
                //            break;
                //        default:
                //            break;
                //    }
                //} 
                #endregion
                //if (cityKid > 0)
                //{
                //    query.And(" h.F_CityKid = {0}", cityKid);
                //}

                if (isStatus >= 0)
                {
                    query.And("a.F_IsStatus={0}", isStatus);
                }
                if (categoryKid > 0)
                {
                    query.And("b.F_CategoryKid={0}", categoryKid);
                }
                if (!string.IsNullOrWhiteSpace(orderNO))
                {
                    query.And("a.F_OrderNo like {0}", "%" + orderNO + "%");
                }
                if (!string.IsNullOrWhiteSpace(productName))
                {
                    query.And("b.F_ProductName like {0}", "%" + productName + "%");
                }
                if (!string.IsNullOrWhiteSpace(brokerName))
                {
                    query.And("h.F_Title like {0}", "%" + brokerName + "%");
                }
                if (!string.IsNullOrWhiteSpace(brokerPhone))
                {
                    query.And("h.F_Phone like {0}", "%" + brokerPhone + "%");
                }
                if (lowPoint > 0)
                {
                    query.And("a.F_PointNum >= {0}", lowPoint);
                }

                if (topPoint > 0)
                {
                    query.And("a.F_PointNum <= {0}", topPoint);
                }
                if (exchangeType > 0)
                {
                    switch (exchangeType)
                    {
                        case 1:
                            query.And("d.F_IsMeskill = {0}", 1);
                            break;
                        case 2:
                            query.And("a.F_ExchangeType='幸运大转盘'");
                            query.And("d.F_IsMeskill = {0}", 0);
                            break;
                        case 3:
                            query.And("a.F_ExchangeType='积分兑换'");
                            query.And("d.F_IsMeskill = {0}", 0);
                            break;
                        default:
                            break;
                    }
                }
                if (!string.IsNullOrWhiteSpace(agentStores))
                {
                    query.And("i.F_Title like {0}", "%" + agentStores + "%");
                }
                if (!string.IsNullOrWhiteSpace(expandingHeader))
                {
                    query.And("j.F_RealName like {0}", "%" + expandingHeader + "%");
                }
                DateTime startTime;
                if (DateTime.TryParse(sdt, out startTime))
                {
                    query.And("a.F_ExchangeTime >='" + startTime + "'");
                }
                DateTime endTime;
                if (DateTime.TryParse(edt, out endTime))
                {
                    endTime = endTime.AddDays(1);
                    query.And("a.F_ExchangeTime <'" + endTime + "'");
                }

                #endregion

                DataTable result = await query.ExecuteDataTable();
                DataTable dt = CreateDataTable(result);
                string path = string.Empty;
                DataTableExcel(dt, string.Format("兑换名单（{0}）.xls", DateTime.Now.ToString("yyyy-MM-dd-hhmmss")), out path);
                //DataTable1Excel(result, GetMapPath("/Document/Template.xls"));
                return path;
            }
        }

        public static DataTable CreateDataTable(DataTable tb)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("CityName", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_ExchangeTime", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_IsStatus", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_ProductName", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_ExchangeNum", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_ProductPrice", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_PointNum", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_Title", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_Phone", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_BrokerCompanyName", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_Address", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_Consignee", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_Mobile", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_CompanyName", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("F_CompanyAddress", System.Type.GetType("System.String")));

            foreach (DataRow dr in tb.Rows)
            {
                string status = ConvertStatus(Convert.ToInt32(dr["F_IsStatus"].ToString()));
                dt.Rows.Add(new string[] { dr["CityName"].ToString(), dr["F_ExchangeTime"].ToString(), status, dr["F_ProductName"].ToString(), dr["F_ExchangeNum"].ToString(), dr["F_ProductPrice"].ToString(), dr["F_PointNum"].ToString(), dr["F_Title"].ToString(), dr["F_Phone"].ToString(), dr["F_BrokerCompanyName"].ToString(), dr["F_Address"].ToString(), dr["F_Consignee"].ToString(), dr["F_Mobile"].ToString(), dr["F_CompanyName"].ToString(), dr["F_CompanyAddress"].ToString(), });
            }
            return dt;
        }

        public static string ConvertStatus(int num)
        {
            switch (num)
            {
                case 1: return "审核中";
                case 2: return "兑换处理中";
                case 3: return "兑换成功";
                case 4: return "审核不通过";
                case 5: return "已使用";
                case 6: return "失效";
                case 7: return "已过期";
                case 8: return "已撤销";
                case 9: return "审核通过";
                case 10: return "已发放";
            }
            return "";
        }
        /// <summary>
        /// 导出Excel文件，并自定义文件名
        /// </summary>
        public static void DataTableExcel(DataTable dtData, String FileName, out string path)
        {
            string sitePath = "Document/";
            string FilePathName = GetMapPath(sitePath);
            DirectoryInfo info = Directory.CreateDirectory(FilePathName);
            FilePathName = FilePathName + FileName;


            using (FileStream fs = new FileStream(FilePathName, FileMode.Create))
            {
                byte[] arrWriteData = Encoding.UTF8.GetBytes(GetDataTableHtml(dtData));
                fs.Write(arrWriteData, 0, arrWriteData.Length);
            }

            //System.IO.File.Delete(FilePathName + FileName);
            //FileStream Fs = new FileStream(FilePathName, FileMode.Create);
            //StreamWriter BWriter = new StreamWriter(Fs, Encoding.UTF8);
            //将DataGrid的信息写入Excel文件 
            //BWriter.Write(GetDataTableHtml(dtData));
            //BWriter.Close();
            //Fs.Close();
            path = "/" + sitePath + FileName;
        }

        public static string GetDataTableHtml(DataTable grid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=UTF-8\"/>");
            sb.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
            sb.Append("<tr><td>所属城市</td><td>订单时间</td><td>订单状态</td><td>兑换商品名称</td><td>兑换的数量</td><td>商品单价</td><td>兑换所需积分</td><td>经纪人姓名</td><td>经纪人电话</td><td>经纪人公司</td><td>经纪人公司地址</td><td>填写的姓名</td><td>填写的联系方式</td><td>填写的公司名称</td><td>填写的公司地址</td></tr>");
            if (grid.Rows.Count > 0)
            {
                foreach (DataRow row in grid.Rows)
                {
                    sb.Append(@"<tr><td>" + (string.IsNullOrWhiteSpace(row["CityName"].ToString()) ? "" : row["CityName"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_ExchangeTime"].ToString()) ? "" : row["F_ExchangeTime"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_IsStatus"].ToString()) ? "" : row["F_IsStatus"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_ProductName"].ToString()) ? "" : row["F_ProductName"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_ExchangeNum"].ToString()) ? "" : row["F_ExchangeNum"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_ProductPrice"].ToString()) ? "" : row["F_ProductPrice"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_PointNum"].ToString()) ? "" : row["F_PointNum"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_Title"].ToString()) ? "" : row["F_Title"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_Phone"].ToString()) ? "" : row["F_Phone"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_BrokerCompanyName"].ToString()) ? "" : row["F_BrokerCompanyName"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_Address"].ToString()) ? "" : row["F_Address"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_Consignee"].ToString()) ? "" : row["F_Consignee"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_Mobile"].ToString()) ? "" : row["F_Mobile"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_CompanyName"].ToString()) ? "" : row["F_CompanyName"]) +
                         "</td><td>" + (string.IsNullOrWhiteSpace(row["F_CompanyAddress"].ToString()) ? "" : row["F_CompanyAddress"]) +
                         "</td></tr>");
                }
            }
            else
            {
                sb.Append(@"<tr><td colspan='5' style='text-align:center'>未能找到相关信息</td></tr>");
            }
            sb.Append("</table>");

            return sb.ToString();
        }

        #region 获得当前绝对路径
        /// <summary>
        /// 获得当前绝对路径
        /// </summary>
        /// <param name="strPath">指定的路径</param>
        /// <returns>绝对路径</returns>
        public static string GetMapPath(string strPath)
        {
            if (strPath.ToLower().StartsWith("http://"))
            {
                return strPath;
            }
            if (HttpContext.Current != null)
            {
                return HttpContext.Current.Server.MapPath(strPath);
            }
            else //非web程序引用
            {
                //    strPath = strPath.Replace("/", "\\");
                //    if (strPath.StartsWith("\\"))
                //    {
                //        strPath = strPath.Substring(strPath.IndexOf('\\', 1)).TrimStart('\\');
                //    }
                //    return System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, strPath);
                strPath = strPath.Replace("/", "\\");
                return AppDomain.CurrentDomain.BaseDirectory + strPath;
            }
        }
        #endregion


        /// <summary>根据经纪人id获取用户兑换数量
        /// </summary>
        /// <param name="userKid">用户id</param>
        /// <param name="productKid">商品id</param>
        /// <returns></returns>
        public static async Task<int> GetUserExchangeCount(string userToken, int productKid)
        {
            if (string.IsNullOrWhiteSpace(userToken)) return 0;
            if (productKid <= 0) return 0;
            const string sql = @"select count(1) from T_ProductsExchange_new where F_UserToken={0} and F_ProductKid={1}";
            using (var db = new DbTour())
            {
                return await db.Sql(sql, userToken, productKid).ExecuteScalar<int>();
            }
        }

        public static async Task<int> GetUserExchangeCount(string userToken, int productKid, DateTime sdt, DateTime edt)
        {
            if (string.IsNullOrWhiteSpace(userToken)) return 0;
            if (productKid <= 0) return 0;
            if (sdt > edt)
            {
                var temp = sdt;
                sdt = edt;
                edt = temp;
            }
            const string sql = @"select count(1) from T_ProductsExchange_new where F_UserToken={0} and F_ProductKid={1} and F_ExchangeTime >{2} and F_ExchangeTime <{3} ";
            using (var db = new DbTour())
            {
                return await db.Sql(sql, userToken, productKid, sdt, edt).ExecuteScalar<int>();
            }

        }

        public static async Task<int> GetListByProductKidCount(int productKid, DateTime beginTime, DateTime endTime)
        {
            if (productKid <= 0) return 0;
            if (beginTime > endTime)
            {
                var temp = beginTime;
                beginTime = endTime;
                endTime = temp;
            }
            const string sql = "select count(1) from T_ProductsExchange_new where F_ProductKid = {0} and F_ExchangeTime >{1} and F_ExchangeTime <{2}";
            using (var db = new DbTour())
            {
                return await db.Sql(sql, productKid, beginTime, endTime).ExecuteScalar<int>(0);
            }
        }

        public static void SendNote(string exchangeKids)
        {
            if (string.IsNullOrWhiteSpace(exchangeKids))
            {
                return;
            }
            string sendNoteApi = Configs.AppSettings["SendNoteApi"].ToString();
            string noteTemplate = Configs.AppSettings["NoteTemplate"].ToString();
            sendNoteApi = sendNoteApi + "/SysConfig/Message/SendSMS";
            //phoneNum = "13031036456";
            string sql = @"
                select  
                    distinct 
                    F_ExchangeTime,
                    a.F_OrderNo,
                    b.F_ProductName,
                    a.F_Mobile,
                    a.F_CompanyName,
                    a.F_CompanyAddress,
                    h.F_Title,
                    H.F_Phone
                from T_ProductsExchange_new a 
                left join T_PointProduct b on a.F_ProductKid=b.Kid                                 
                left join T_OAuthInfo g on a.F_UserToken=g.F_UserToken
                left join T_Broker h on g.F_BrokerKid=h.kid
                where a.kid in  (" + exchangeKids + ")";
            List<T_ProductsExchangeExpand> list = new List<T_ProductsExchangeExpand>();
            using (var db = new DbTour())
            {
                list = db.Sql(sql).ToList<T_ProductsExchangeExpand>().Result;
            }
            foreach (T_ProductsExchangeExpand item in list)
            {
                string smsContent = string.Format(noteTemplate, item.F_Title, item.F_ExchangeTime.ToString("MM月dd日"), item.F_ProductName); ;
                string postData = item.F_Phone + "&" + smsContent;
                Dictionary<string, string> dic = new Dictionary<string, string>();
                dic.Add("phone", item.F_Phone);
                dic.Add("smsContent", smsContent);
                Top.Common.TopWebUtils webUtils = new Top.Common.TopWebUtils();
                webUtils.DoGet(sendNoteApi, dic);
            }
        }

        public static void SendNote(string customerName, DateTime orderTime, string productName, string phoneNum)
        {
            if (string.IsNullOrWhiteSpace(phoneNum) || string.IsNullOrWhiteSpace(customerName) || customerName == "undefined" || phoneNum == "undefined")
            {
                return;
            }
            string sendNoteApi = Configs.AppSettings["SendNoteApi"].ToString();
            string noteTemplate = Configs.AppSettings["NoteTemplate"].ToString();
            sendNoteApi = sendNoteApi + "/SysConfig/Message/SendSMS";
            //phoneNum = "13031036456";
            string smsContent = string.Format(noteTemplate, customerName, orderTime.ToString("MM月dd日"), productName); ;
            string postData = phoneNum + "&" + smsContent;
            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("phone", phoneNum);
            dic.Add("smsContent", smsContent);
            Top.Common.TopWebUtils webUtils = new Top.Common.TopWebUtils();
            webUtils.DoGet(sendNoteApi, dic);
        }
    }
}
